package com.conversationboard.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.joda.time.DateTime;

import com.conversationboard.database.Database;
import com.conversationboard.database.Queries;
import com.conversationboard.model.enums.SearchType;

public class Search {

    public List<SearchResult> search(String terms, int offset, SearchType searchType) throws SQLException {

        Connection connection = Database.getConnection();

        List<SearchResult> results;

        try {

            String query = Queries.SEARCH_THREADS_QUERY;

            switch (searchType) {
            case TITLE:
                query = Queries.SEARCH_THREADS_QUERY;
                break;
            case DISPLAYNAME:
                query = Queries.SEARCH_THREADS_FOR_DISPLAY_NAME_QUERY;
                break;
            case COMBINED:
                query = Queries.SEARCH_COMBINED_QUERY;
                break;
            }

            PreparedStatement statement = connection.prepareStatement(query);

            statement.setString(1, terms);
            statement.setString(2, terms);
            statement.setInt(3, offset);
            statement.executeQuery();

            ResultSet resultSet = statement.getResultSet();

            results = new ArrayList<SearchResult>();

            while (resultSet.next()) {
                int boardId = resultSet.getInt("boardid");
                int threadId = resultSet.getInt("threadid");
                String title = resultSet.getString("title");

                SearchResult result = new SearchResult();
                result.setBoardId(boardId);
                result.setThreadId(threadId);
                result.setThreadTitle(title);

                results.add(result);
            }

            resultSet.close();

        } finally {
            Database.close(connection);
        }

        return results;
    }


    /**
     * Search through message content
     * 
     * @param terms the terms of the search
     * @param offset the place to start in the result set (used for paging results)
     * @return a list of search results
     * @throws SQLException
     */

    public List<SearchResult> fullSearch(String terms, int offset) throws SQLException {

        Connection connection = Database.getConnection();

        List<SearchResult> results;

        try {

            PreparedStatement statement = connection.prepareStatement(Queries.SEARCH_QUERY);

            statement.setString(1, "%" + terms + "%");
            statement.setInt(2, offset);
            statement.executeQuery();

            ResultSet resultSet = statement.getResultSet();

            results = new ArrayList<SearchResult>();

            while (resultSet.next()) {
                int threadId = resultSet.getInt("threadid");
                int boardId = resultSet.getInt("boardid");
                String title = resultSet.getString("title");
                String userName = resultSet.getString("messages.displayname");
                String body = resultSet.getString("body");

                SearchResult result = new SearchResult();
                result.setBoardId(boardId);
                result.setThreadId(threadId);
                result.setThreadTitle(title);
                result.setUserName(userName);
                result.setBody(body);

                results.add(result);
            }

            resultSet.close();

        } finally {
            Database.close(connection);
        }

        return results;
    }


    public List<SearchResult> dateSearch(String date) throws SQLException {

        Connection connection = Database.getConnection();

        List<SearchResult> results;

        try {

            results = new ArrayList<SearchResult>();

            if (!date.trim().matches("^[0-9]{4}-[0-9]{2}-[0-9]{2}$")) {
                return results;
            }

            PreparedStatement statement = connection.prepareStatement(Queries.SEARCH_BY_DATE);

            statement.setString(1, date + " 00:00:00");
            statement.setString(2, date + " 23:59:59");
            statement.executeQuery();

            ResultSet resultSet = statement.getResultSet();

            while (resultSet.next()) {
                int boardId = resultSet.getInt("boardid");
                int threadId = resultSet.getInt("threadid");
                String title = resultSet.getString("title");

                SearchResult result = new SearchResult();
                result.setBoardId(boardId);
                result.setThreadId(threadId);
                result.setThreadTitle(title);

                results.add(result);
            }

            resultSet.close();

        } finally {
            Database.close(connection);
        }

        return results;

    }


    /**
     * Method commented out, as it's not going to be used until InnoDB. The method and entire function needs refactored to pass the viewing viewer in so that
     * time zones work correctly.
     */

    // public List<SearchResult> userSearch(String displayName, int month, int
    // year, int offset) throws SQLException {
    //
    // Connection connection = Database.getConnection();
    //
    // List<SearchResult> results;
    //
    // try {
    //
    // PreparedStatement statement =
    // connection.prepareStatement(Queries.SEARCH_BY_USER);
    //
    // int lastDayOfMonth = DateFormatter.getLastDayOfMonth(year, month);
    //
    // statement.setString(1, displayName);
    // String start = year + "-" + month + "-1 00:00:00";
    // String end = year + "-" + month + "-" + lastDayOfMonth + " 23:59:59";
    //
    // statement.setString(2, start);
    // statement.setString(3, end);
    // statement.setInt(4, offset);
    // statement.executeQuery();
    //
    // ResultSet resultSet = statement.getResultSet();
    //
    // results = new ArrayList<SearchResult>();
    //
    // while (resultSet.next()) {
    // int boardId = resultSet.getInt("boardid");
    // int threadId = resultSet.getInt("threadid");
    // String body = resultSet.getString("body");
    // Date timestamp = resultSet.getTimestamp("timestamp");
    // boolean deleted = resultSet.getBoolean("deleted");
    //
    // if (!deleted) {
    // SearchResult result = new SearchResult();
    // result.setBoardId(boardId);
    // result.setThreadId(threadId);
    //
    // result.setThreadTitle("Posted on: " +
    // DateFormatter.prettyPrint(timestamp));
    // result.setBody(body);
    //
    // results.add(result);
    // }
    // }
    //
    // resultSet.close();
    //
    // } finally {
    // Database.close(connection);
    // }
    //
    // return results;
    // }
    /**
     * Return a list of thread IDs for a given year and month. This is really a search function and supports paging. It will return a maximum of 100 threads,
     * and the threads that are returned start at the offset that is passed in.
     * 
     * @param year
     * @param month
     * @param offset the amount by which to offset the search.
     * @return
     */

    public List<SearchResult> getThreadsInMonth(int year, int month, int offset) throws SQLException {

        Connection connection = null;

        try {

            List<SearchResult> results = new ArrayList<SearchResult>(100);

            connection = Database.getConnection();

            DateTime startDate = new DateTime(year, month, 1, 0, 0, 0, 0);
            DateTime endDate = startDate.plusMonths(1);

            PreparedStatement statement = connection.prepareStatement(Queries.GET_THREADS_IN_MONTH);
            statement.setDate(1, new java.sql.Date(startDate.toDate().getTime()));
            statement.setDate(2, new java.sql.Date(endDate.toDate().getTime()));
            statement.setInt(3, offset);

            statement.execute();

            ResultSet resultSet = statement.getResultSet();

            while (resultSet.next()) {
                SearchResult searchResult = new SearchResult();
                searchResult.setBoardId(resultSet.getInt("boardid"));
                searchResult.setThreadId(resultSet.getInt("threadid"));
                searchResult.setThreadTitle(resultSet.getString("title"));
                searchResult.setUserName(resultSet.getString("displayname"));

                results.add(searchResult);
            }

            return results;

        } finally {
            Database.close(connection);
        }

    }

}
